﻿using System;
using System.Collections.Generic;
////using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management;
using System.Collections.Specialized;
using System.Threading;
using System.Windows.Forms;
using System.Drawing;

using Fireball.Windows.Forms; 
using Fireball.Syntax; 
using Fireball.CodeEditor.SyntaxFiles;
using DBConnectionLib;
using System.Data;


namespace DBSourceToolsLib.SysObjects
{
    public class SOTable : SysObject//, Interfaces.IDisplayPanel
    {
        //public class SOTableColumn
        //{
        //    public string ColumnName = "";
        //    public bool inPrimaryKey = false;
        //    public bool isComputed = false;
        //}

        //List<SOTableColumn> m_ColumnList = new List<SOTableColumn>();
        protected DataSets.TableColumnsDS m_oColumnListDs = new DBSourceToolsLib.DataSets.TableColumnsDS();

        public SOTable(string strName, string strBaseDirectory, Database oDatabase, string strUrn)
            : base(strName, strBaseDirectory, oDatabase, SysObjectType.Table)
        {
            this.Urn = strUrn;
            //LoadScript(oDatabase);
        }
        public SOTable(string strFileName, Database oDatabase)
            : base(SysObjectType.Table, strFileName, oDatabase)
        {
            // we are loading this from disk - ensure we load columns from disk as well.
            string strColFileName = this.BaseDirectory + "\\Tables\\Columns\\" + this.Name + "_Columns.xml";
            if (System.IO.File.Exists(strColFileName))
                this.m_oColumnListDs.ReadXml(strColFileName);
        }

        private void LoadColumnsFromFile(string strColFileName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public SOTable(SOTable oldSysObject, Database targetDatabase)
            :base ( oldSysObject.Name, targetDatabase.BaseDirectory, targetDatabase, SysObjectType.Table)
        {
            this.Text = oldSysObject.Text;
            this.Text = this.Text.Replace("[" + oldSysObject.DBName + "]", "[" + targetDatabase.DatabaseName + "]");
        }

        public override void LoadScript(Database oDatabase, string outputFileName)
        {

        }

        #region IDisplayPanel Members

        public override System.Windows.Forms.Panel GetPanel()
        {
            Panel oPanel = new Panel();
            oPanel.Dock = DockStyle.Fill;

            CodeEditorControl codeEdit = new CodeEditorControl();
            codeEdit.Dock = DockStyle.Fill;

            CodeEditorSyntaxLoader.SetSyntax(codeEdit, SyntaxLanguage.SqlServer2K);
            codeEdit.Document.Text = this.Text;
            oPanel.Controls.Add(codeEdit);


            return oPanel;
        }

        #endregion

        internal void LoadScript(Database oDatabase, string outputFileName, Microsoft.SqlServer.Management.Smo.Table oSmo)
        {
            base.EnsureDirectoryExists(outputFileName);

            Microsoft.SqlServer.Management.Smo.ScriptingOptions oScriptingOptions = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
            oScriptingOptions.ClusteredIndexes = true;
            oScriptingOptions.NonClusteredIndexes = true;
            oScriptingOptions.DriAll = true;
            oScriptingOptions.Indexes = true;
            oScriptingOptions.IncludeDatabaseContext = false;
            oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
            oScriptingOptions.Triggers = true;
            oScriptingOptions.NoCollation = true;
            oScriptingOptions.FileName = outputFileName;
            oScriptingOptions.ExtendedProperties = UserSettings.Instance().UseExtendedProperties;
            oScriptingOptions.AnsiFile = UserSettings.Instance().UseANSII;

            //oScriptingOptions.AnsiFile = true;

            StringBuilder oStringBuilder = new StringBuilder();
            StringCollection myStrColl = new StringCollection();
            myStrColl = oSmo.Script(oScriptingOptions);

            base.SetTextFromFile(outputFileName);

            this.m_oColumnListDs.TableColumns.Clear();

            foreach (Microsoft.SqlServer.Management.Smo.Column oCol in oSmo.Columns)
            {
                DataSets.TableColumnsDS.TableColumnsRow newRow = m_oColumnListDs.TableColumns.NewTableColumnsRow();
                newRow.ColumnName = oCol.Name;
                newRow.isComputed = oCol.Computed;
                newRow.inPrimaryKey = oCol.InPrimaryKey;
                newRow.Type = oCol.DataType.ToString();
                newRow.isIdentity = oCol.Identity.ToString();
                newRow.isNullable = oCol.Nullable.ToString();
                m_oColumnListDs.TableColumns.AddTableColumnsRow(newRow);
                
            }
            m_oColumnListDs.AcceptChanges();

            string strColumnsFile = outputFileName.Substring(0, outputFileName.LastIndexOf("\\") + 1);
            strColumnsFile += "Columns\\" + this.Name + "_Columns.xml";
            base.EnsureDirectoryExists(strColumnsFile);
            FileUtils.EnsureFileIsWriteable(strColumnsFile);
            m_oColumnListDs.WriteXml(strColumnsFile);

        }

        protected override void WriteData(string strDirPath, string strOrderBy, bool isDeploymentTarget, bool bRefreshData)
        {
            if (!bRefreshData)
                return;
            string dataFile = strDirPath + "\\Data";
            if (!System.IO.Directory.Exists(dataFile))
                System.IO.Directory.CreateDirectory(dataFile);

            DataSet oDS = new DataSet();

            // enclose the table name with [] i.e. [HumanResources].[Department]
            string strSelectQuery = this.GetSelectQueryForData(strOrderBy);

            //Database oDB = UserSettings.Instance().getDatabaseEx(this.m_Database.NickName);
            if (m_Database!= null)
            {
                if (!isDeploymentTarget)
                {
                    if (m_Database.SQLVersion.CompareTo("SQL7") != 0)
                    {
                        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(m_Database.ConnectionString))
                        {
                            conn.Open();
                            System.Data.SqlClient.SqlDataAdapter oDA = new System.Data.SqlClient.SqlDataAdapter();
                            oDA.SelectCommand = new System.Data.SqlClient.SqlCommand(strSelectQuery, conn);
                            oDA.Fill(oDS, "mydata");
                            conn.Close();
                        }
                    }
                    else
                    {
                        using (System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(m_Database.ConnectionString))
                        {

                            oConn.Open();
                            System.Data.OleDb.OleDbDataAdapter oDataAdapter = new System.Data.OleDb.OleDbDataAdapter();
                            oDataAdapter.SelectCommand = new System.Data.OleDb.OleDbCommand(strSelectQuery, oConn);
                            oDataAdapter.Fill(oDS, "mydata");
                            oConn.Close();
                        }

                    }

                    string strSchemaFile = dataFile + "\\" + this.Name + "_Schema.xml";
                    string strDataFile = dataFile + "\\" + this.Name + ".xml";
                    FileUtils.EnsureFileIsWriteable(strSchemaFile);
                    FileUtils.EnsureFileIsWriteable(strDataFile);
                    oDS.WriteXml(strDataFile);
                    oDS.WriteXmlSchema(strSchemaFile);
                }
            }
        }

        public string GetSelectQuery(string strOrderBy, bool bIncludeComputed)
        {
            StringBuilder oBuilder = new StringBuilder();
            oBuilder.AppendLine("select ");
            //string strQuery = "select ";//[" + this.Name + "]";
            bool isFirstCol = true;

            foreach (DBSourceToolsLib.DataSets.TableColumnsDS.TableColumnsRow row in
                this.m_oColumnListDs.TableColumns.Rows)
            {
                if ((!row.isComputed) && bIncludeComputed)
                {
                    if (isFirstCol)
                    {
                        oBuilder.AppendLine("\t[" +row.ColumnName + "] ");
                        isFirstCol = false;
                    }
                    else
                    {
                        oBuilder.AppendLine("\t,[" + row.ColumnName + "]");
                    }
                }
            }
            oBuilder.AppendLine("from ");
            oBuilder.AppendLine("\t" + GetFullyQualifiedName());
            
            // add order by clause if neccessary
            if (strOrderBy != null)
            {
                oBuilder.AppendLine(" order by " + strOrderBy);
            }
            return oBuilder.ToString();
        }

        private string GetSelectQueryForData(string strOrderBy)
        {
            string strQuery = "select ";//[" + this.Name + "]";
            bool isFirstCol = true;

            foreach (DBSourceToolsLib.DataSets.TableColumnsDS.TableColumnsRow row in
                this.m_oColumnListDs.TableColumns.Rows)
            {
                if (!row.isComputed)
                {
                    if (isFirstCol)
                    {
                        strQuery += "[" + row.ColumnName + "] ";
                        isFirstCol = false;
                    }
                    else
                    {
                        strQuery += ",[" + row.ColumnName + "]";
                    }
                }
            }
            strQuery += " from " + GetFullyQualifiedName();


            // add order by clause if neccessary
            if (strOrderBy != null)
            {
                strQuery += " order by " + strOrderBy;
            }
            return strQuery;
        }



        public override List<ColInfo> Columns
        {
            get {
                List<ColInfo> strList = new List<ColInfo>();
                foreach (DBSourceToolsLib.DataSets.TableColumnsDS.TableColumnsRow oRow in
                    this.m_oColumnListDs.TableColumns.Rows)
                {
                    ColInfo oInfo = new ColInfo();
                    oInfo.Name = oRow.ColumnName;
                    oInfo.Type = oRow.Type;
                    oInfo.PrimaryKey = oRow.inPrimaryKey;
                    if (!oRow.IsisIdentityNull())
                        oInfo.IsIdentity = Boolean.Parse(oRow.isIdentity);
                    else
                        oInfo.IsIdentity = false;
                    if (!oRow.IsisNullableNull())
                        oInfo.IsNullable = Boolean.Parse(oRow.isNullable);
                    else
                        oInfo.IsNullable = false;
                    strList.Add(oInfo);
                }
                return strList;
            }
        }



        internal char[] GetUpdateQuery()
        {
            throw new NotImplementedException();
        }

        public override DBSourceToolsLib.Composite.DBComposite GetDBComposite()
        {
            
            Composite.DBComposite oTableComposite = 
                new Composite.DBComposite(this.Name, this.NameNoSchema,
                    Composite.DBCompositeType.Table,
                    new Composite.TreeNodeTag(this.m_Database.NickName,
                    "File", this.FileName, this.QualifiedName));
            

            Composite.DBComposite oDataComposite =
                new Composite.DBComposite("Data","Data",
                    Composite.DBCompositeType.Data,
                    new Composite.TreeNodeTag(this.m_Database.NickName,
                    "Data", this.Name, this.QualifiedName));
            oTableComposite.Add(oDataComposite);

            Composite.DBComposite oFieldsComposite =
                new Composite.DBComposite("Fields","Fields",
                    Composite.DBCompositeType.Fields,
                    new Composite.TreeNodeTag(this.m_Database.NickName,
                    "Fields", this.Name, this.QualifiedName));

            oTableComposite.Add(oFieldsComposite);

            foreach (ColInfo oColInfo in this.Columns) {
                Composite.DBComposite oField = new Composite.DBComposite( oColInfo.NameAndType,oColInfo.Name,
                    Composite.DBCompositeType.Field,
                    new Composite.TreeNodeTag( this.m_Database.NickName, "Column", oColInfo.Name, null)
                    );
                oFieldsComposite.Add(oField);
            }
            return oTableComposite;
        }
    }
}
